﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SAPbouiCOM;

namespace BoAddonBiz_FreightForwarder.Custom
{
    public class ImportExcelData
    {
        #region private methods
        /// <summary>
        /// 把Excel数据导入到系统中
        /// </summary>
        public static void ExcelDataImportSystem(SAPbouiCOM.Form CurrentForm, string InOut, string BusType)
        {
            SAPBoAddon.B1Assistant.WinFormDialogSetting dialogsetting = new SAPBoAddon.B1Assistant.WinFormDialogSetting();
            dialogsetting.Title = "请选择报关单";
            dialogsetting.Filter = "*.xls|*.xls|*.xlsx|*.xlsx";
            dialogsetting.FileName = AppDomain.CurrentDomain.DynamicDirectory;
            SAPBoAddon.B1Assistant.WinForm.WinFormDialogSetting = dialogsetting;
            //SAPBoAddon.B1Assistant.WinForm.WinForm_ShowOpenFileDialog();
            string FilePath = SAPBoAddon.B1Assistant.WinForm.WinForm_GetOpenFileName();
            if (string.IsNullOrEmpty(FilePath)) return;
            GetExcelData import = new GetExcelData();
            var customs = import.GetData(FilePath);
            if (SAPBoAddon.B1Addon.B1Addon.SBOCompany.InTransaction == false) SAPBoAddon.B1Addon.B1Addon.SBOCompany.StartTransaction();
            SAPbobsCOM.Recordset rs = SAPBoAddon.B1Addon.B1Addon.SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
            string SQL = "UPDATE [{0}] SET {1} Where DocEntry = {2}";
            string DocEntry = SAPBoAddon.B1Assistant.Item.GetValue(CurrentForm, "3").ToString();
            string TableName = InOut == "I" ? "@AVA_" + BusType + "_OCTC" : "@AVA_" + BusType + "_OATC";
            #region 字段赋值
            //预录编号
            string Fields = "U_CustomsDeclaration = '" + customs.UniqueId + "',";
            //申报地海关
            Fields += "U_ApplyCustoms = '" + customs.ApplyCustoms + "',";
            //报关员
            Fields += "U_CustomsBroker = '" + customs.Broker + "',";
            Fields += "U_CustomsBrokerCode = 'V000300',U_CustomsBrokerName = '北京海翔国际货运代理有限公司报关部',U_CardRegCode='1105980157',";
            //经营单位十位海关注册码
            Fields += "U_CusRegCode = '" + customs.Company + "',";

            //经管单位名称
            rs.DoQuery("SELECT CardCode,CardName FROM OCRD Where CardName = '" + customs.CompanyName + "'");
            if (rs.RecordCount > 0)
            {
                Fields += "U_CompanyCode = '" + rs.Fields.Item("CardCode").Value + "',";
                Fields += "U_CompanyName = '" + rs.Fields.Item("CardName").Value + "',";
            }
            else
            {
                Fields += "U_CompanyName = '" + customs.CompanyName + "',";
            }
            //合同号
            Fields += "U_ContractNo = '" + customs.Contract + "',";
            //海关编码
            //Fields += "U_CustomsDeclaration = '" + customs.CustomsNo + "',";
            //进出口日期
            Fields += "U_ExImDate = '" + customs.Datetime + "',";
            //申报日期
            Fields += "U_ReportingDate = '" + customs.DeclaredDate + "',";

            //收货单位海关注册码
            Fields += "U_DeliverRegCode = '" + customs.DeliveryCompany + "',";
            //收货单位
            rs.DoQuery("SELECT CardCode,CardName FROM OCRD Where CardName = '" + customs.DeliveryCompanyName + "'");
            if (rs.RecordCount > 0)
            {
                Fields += "U_DeliverCode = '" + customs.DeliveryCompany + "',";
                Fields += "U_DeliverName = '" + customs.DeliveryCompanyName + "',";
                rs.DoQuery("UPDATE OCRD SET U_CusRegCode = '" + customs.Company + "' Where CardCode = '" + customs.DeliveryCompany + "'");
            }
            else
            {
                Fields += "U_DeliverName = '" + customs.DeliveryCompanyName + "',";
            }
            //启运国
            Fields += "U_TransportCountry = '" + customs.DepartureCountry + "',";
            //启运国代码
            Fields += "U_TransportCountryC = '" + customs.DepartureCountryCode + "',";
            //启运港
            Fields += "U_LoadingPort = '" + customs.DeparturePort + "',";
            //启运港代码
            Fields += "U_LoadingPortCode = '" + customs.DeparturePortCode + "',";
            //目的地
            rs.DoQuery("select Code from [@DISTRICT] where Name = '" + customs.Destination + "'");
            if (rs.RecordCount > 0)
                Fields += "U_InlandSource = '" + rs.Fields.Item("Code").Value + "',";
            Fields += "U_TransferType = '001',";
            Fields += "U_DocumentType = '001',";
            Fields += "U_CustomsStyleType = '" + customs.CustomsStyleType + "',";
            //随附单据
            //Fields += "U_CustomsDeclaration = '" + customs.Documents + "',";
            //录入员
            //Fields += "U_CustomsBroker = '" + customs.Entry + "',";
            //输入单位
            //Fields += "U_CustomsDeclaration = '" + customs.EntryCompany + "',";
            //运费
            Fields += "U_FreightCharges = '" + customs.FreightFlag + "',";
            Fields += "U_FCRate = '" + (string.IsNullOrEmpty(customs.FreightCharges) ? "0" : customs.FreightCharges) + "',";
            Fields += "U_FCCurrency = '" + customs.FreightCurrency + "',";
            //保费
            Fields += "U_SafeCharges = '" + customs.PremiumsFlag + "',";
            Fields += "U_SafeRate = '" + (string.IsNullOrEmpty(customs.Premiums) ? "0" : customs.Premiums) + "',";
            Fields += "U_SafeCurrency = '" + customs.PremiumsCurrency + "',";
            //杂费
            Fields += "U_OtherCharges = '" + customs.OtherCharges + "',";
            Fields += "U_OtherRate = '" + (string.IsNullOrEmpty(customs.OtherCharges) ? "0" : customs.OtherCharges) + "',";
            Fields += "U_OtherCurrency = '" + customs.OtherChargesCurrency + "',";
            //毛重
            Fields += "U_TotalWeight = '" + (string.IsNullOrEmpty(customs.GrossWeight) ? "0" : customs.GrossWeight) + "',";
            //批文号
            Fields += "U_JobOfferNo = '" + customs.JobOffer + "',";
            //许可证号
            Fields += "U_LicenseNo = '" + customs.License + "',";
            //征免性质
            rs.DoQuery("SELECT Code FROM [@Property] Where Name = '" + customs.Natrue + "'");
            if (rs.RecordCount > 0)
                Fields += "U_Property = '" + rs.Fields.Item("Code").Value + "',";
            //包装类型
            if (customs.PackageType == "其他")
            {
                customs.PackageType = "其它";
            }
            rs.DoQuery("SELECT Code FROM [@Package] Where Name = '" + customs.PackageType + "'");
            if (rs.RecordCount > 0)
                Fields += "U_PackageDescription = '" + rs.Fields.Item("Code").Value + "',";
            //口岸
            Fields += "U_PortCode = '" + customs.Port + "',";
            //件数
            Fields += "U_TotalNumber = '" + (string.IsNullOrEmpty(customs.Quantity) ? "0" : customs.Quantity) + "',";
            //备案号
            Fields += "U_RecordNo = '" + customs.RecordNo + "',";
            //放行日期
            Fields += "U_ReleaseDate = '" + customs.ReleaseDate + "',";
            //唛头（从业务单上取）
            //Fields += "U_ = '" + customs.ShippingMark + "',";
            //净重
            Fields += "U_TotalNetWeight = '" + (string.IsNullOrEmpty(customs.SuttleWeight) ? "0" : customs.SuttleWeight) + "',";
            //税费征收情况
            //Fields += "U_CustomsDeclaration = '" + customs.Tax + "',";
            //征收比例
            Fields += "U_CollectingRate = '" + (string.IsNullOrEmpty(customs.TaxScale) ? "0" : customs.TaxScale) + "',";
            //贸易方式
            rs.DoQuery("SELECT Code FROM [@AVA_FF_OTRM] Where Name = '" + customs.TradeMothds + "'");
            if (rs.RecordCount > 0)
                Fields += "U_TradeMethod = '" + rs.Fields.Item("Code").Value + "',";
            //成交方式
            Fields += "U_BargainMethod  = '" + customs.TradeTerms + "',";
            //运输方式
            rs.DoQuery("SELECT Code FROM [@ModesOfTransport] Where Name = '" + customs.Transport + "'");
            if (rs.RecordCount > 0)
                Fields += "U_ModeOfTransport = '" + rs.Fields.Item("Code").Value + "',";
            //运输工具
            Fields += "U_Transportation = '" + customs.Transportation + "'";
            //用途
            //Fields += "U_CustomsDeclaration = '" + customs.Usage + "',";
            //Fields += "U_CustomsDeclaration = '" + customs.Waybill + "'";
            #endregion
            SQL = string.Format(SQL, TableName, Fields, DocEntry);
            rs.DoQuery(SQL);
            #region lines
            int n = 1;
            string ObjectCode = InOut == "I" ? "AVA_" + BusType + "_Clearance" : "AVA_" + BusType + "_ApplyCustoms";
            string TableNameLine = InOut == "I" ? "@AVA_" + BusType + "_CTC1" : "@AVA_" + BusType + "_ATC1";
            SQL = "DELETE FROM [{0}] Where DocEntry = {1}";
            SQL = string.Format(SQL, TableNameLine, DocEntry);
            rs.DoQuery(SQL);
            foreach (var line in customs.Lines)
            {
                string nature = string.Empty;
                rs.DoQuery("SELECT Code FROM [@Property] Where Name = '" + line.Nature + "'");
                if (rs.RecordCount > 0)
                    nature = rs.Fields.Item("Code").Value;
                string Country = string.Empty;
                rs.DoQuery("SELECT U_ForeignName FROM OCRY Where Name = '" + line.Country + "'");
                if (rs.RecordCount > 0)
                    Country = rs.Fields.Item("U_ForeignName").Value;
                string FirstUnit = string.Empty;
                rs.DoQuery("SELECT Code FROM [@AVA_FF_OPRI] Where Name = '" + line.FirstUnit + "' And ISNULL(U_Type,'') = ''");
                if (rs.RecordCount > 0)
                {
                    FirstUnit = rs.Fields.Item("Code").Value;
                }
                string SecondUnit = string.Empty;
                rs.DoQuery("SELECT Code FROM [@AVA_FF_OPRI] Where Name = '" + line.SecondUnit + "' And ISNULL(U_Type,'') = ''");
                if (rs.RecordCount > 0)
                {
                    SecondUnit = rs.Fields.Item("Code").Value;
                }
                string ThirdUnit = string.Empty;
                rs.DoQuery("SELECT Code FROM [@AVA_FF_OPRI] Where Name = '" + line.ThirdUnit + "' And ISNULL(U_Type,'') = ''");
                if (rs.RecordCount > 0)
                {
                    ThirdUnit = rs.Fields.Item("Code").Value;
                }
                SQL = "INSERT INTO [{0}] (DocEntry,LineId,VisOrder,Object,U_HSCode,U_ProductName,U_Norms,U_FirstQuantity,U_FirstUnit,U_SecondQuantity,U_SecondUnit,U_Quantity,U_Unit,U_Currency,U_ExemptionOfNature,U_Origin,U_Total,U_Manufacturer,U_Price) values({1})";
                Fields = DocEntry + "," + n.ToString() + "," + n.ToString() + ",'" + ObjectCode + "','" + line.ItemCode + "','"
                    + line.Description + "','"
                    + line.Standard + "',"
                    + line.FirstQuantity + ",'" + FirstUnit + "',"
                    + line.SecondQuantity + ",'" + SecondUnit + "',"
                    + line.ThirdQuantity + ",'" + ThirdUnit + "','"
                    + line.Currency + "','"
                    + nature + "','"
                    + Country + "'," + line.Amount + ",'11',"
                    + line.Price;
                SQL = string.Format(SQL, TableNameLine, Fields);
                rs.DoQuery(SQL);
                n++;
            }
            #endregion
            if (SAPBoAddon.B1Addon.B1Addon.SBOCompany.InTransaction) SAPBoAddon.B1Addon.B1Addon.SBOCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit);
            CurrentForm.Mode = BoFormMode.fm_FIND_MODE;
            SAPBoAddon.B1Assistant.Item.SetEnabled(CurrentForm, "3", true);
            SAPBoAddon.B1Assistant.Item.SetValue(CurrentForm, "3", DocEntry);
            CurrentForm.Items.Item("1").Click();
            SAPBoAddon.B1Addon.B1Addon.SBOApp.MessageBox("导入成功！");
        }
        #endregion
    }
}
